<?php
namespace Admin\Controller;
use Think\Controller;
use PHPExcel_IOFactory;
use PHPExcel;
class ExcelController extends CommonController {

public function upload(){

        $upload=new \Think\Upload();                                //实例化上传类
        $upload->maxSize   =     3145728;                           //设置附件上传大小
        $upload->exts      =     array('xlsx','xls');               //设置附件上传类型
        $upload->rootPath  =      './Upload/';                     //设置附件上传根目录(没有则需手动新建)
        $upload->savePath  =      '';                               //设置附件上传（子）目录
        //上传文件
        $info   =   $upload->upload();
        if(!$info){                                                 //上传错误提示错误信息
          $this->error($upload->getError());

        }else{                                                      //上传成功获取上传文件信息
       foreach($info as $file){
        //echo $file['savepath'].$file['savename'];
        //这里的路径为网站根目录下Uploads/2016-02-19/56c7056f732ff.xlsx
        $filePath="Upload/".$file['savepath'].$file['savename'];
        //等价于
        //$filePath = __ROOT__."Uploads/" .$file['savepath'].$file['savename'];
    }
        echo "<h3>".$filePath."文件上传成功！</h3><p>";
       //上传成功则开始导入到mysql中
        $result=$this->importExcel($filePath);
        echo $result['message'];
}
}



    public function importExcel($file){
        //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
        /*import("Org.Util.PHPExcel");
        import("Org.Util.PHPExcel.Writer.Excel5");
        import("Org.Util.PHPExcel.IOFactory.php");*/
        
        import("Org.Util.PHPExcel");  
        import("Org.Util.PHPExcel.Writer.Excel5", '', '.php');  
        import("Org.Util.PHPExcel.IOFactory", '', '.php');  

        if(!file_exists($file)){
            return array("error"=>0,'message'=>'file not found!');
        }
        vendor("PHPExcel.Classes.PHPExcel.IOFactory");
        //出现：Class 'Admin\Controller\PHPExcel_IOFactory' not found
        //注意这儿加了一个"\"表示调用公共空间，也可以理解为顶层
        //$objReader = \PHPExcel_IOFactory::createReader('Excel5');
        //获取excel文件:获取Excel第1张表即（Sheet1）
        $objPHPExcel= \PHPExcel_IOFactory::load($file);
        //$objPHPExcel= \PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet1=$objPHPExcel->getSheet(0);
        //获取行数，并把数据读取出来$data数组
        $rowCount=$sheet1->getHighestRow();//excel行数
        $data=array();
        for($i=2;$i<=$rowCount;$i++){

            $item['idCard']=$sheet1->getCellByColumnAndRow(1,$i)->getValue();
            $item['studentID']=$sheet1->getCellByColumnAndRow(2,$i)->getValue();
            $item['studentName']=$sheet1->getCellByColumnAndRow(3,$i)->getValue();
            $item['forecast']=$sheet1->getCellByColumnAndRow(4,$i)->getValue();
            $item['studentSex']=$sheet1->getCellByColumnAndRow(5,$i)->getValue();
            $item['studentNumber']=$sheet1->getCellByColumnAndRow(6,$i)->getValue();
            $item['dormitoryGrade']=$sheet1->getCellByColumnAndRow(7,$i)->getValue();
            $item['dormitory']=$sheet1->getCellByColumnAndRow(8,$i)->getValue();
            $item['report']=$sheet1->getCellByColumnAndRow(9,$i)->getValue();
            $item['address']=$sheet1->getCellByColumnAndRow(10,$i)->getValue();
            $item['sick']=$sheet1->getCellByColumnAndRow(11,$i)->getValue();
            $item['prove']=$sheet1->getCellByColumnAndRow(12,$i)->getValue();
            $item['insurance']=$sheet1->getCellByColumnAndRow(13,$i)->getValue();
            $item['className']=$sheet1->getCellByColumnAndRow(14,$i)->getValue();
            $item['speName']=$sheet1->getCellByColumnAndRow(15,$i)->getValue();
            $data[]=$item;
        }
        $success=0;
        $error=0;
        $sum=count($data);
        foreach($data as $k=>$v){
            if(M('pro')->data($v)->add()){
                $success++;
            }else{
                $error++;
            }
        }

        echo "<h3>总{$sum}条，成功{$success}条，失败{$error}条</h3>";
        return array("error"=>0,'message'=>'import is succussful!');
    }


/*    public function upload() {
ini_set('memory_limit','1024M');
if (!empty($_FILES)) {
$config = array(
'exts' => array('xlsx','xls'),
'maxSize' => 3145728000,
'rootPath' =>"./Public/",
'savePath' => 'Uploads/',
'subName' => array('date','Ymd'),
);
$upload = new \Think\Upload($config);
if (!$info = $upload->upload()) {
$this->error($upload->getError());
}
vendor("PHPExcel.PHPExcel");
$file_name=$upload->rootPath.$info['photo']['savepath'].$info['photo']['savename'];
$extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表格后缀格式
if ($extension == 'xlsx') {
$objReader =\PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
} else if ($extension == 'xls'){
$objReader =\PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
}
$sheet =$objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();//取得总行数
$highestColumn =$sheet->getHighestColumn(); //取得总列数
//D('student')->execute('truncate table student');
for ($i = 2; $i <= $highestRow; $i++) {
//看这里看这里,前面小写的a是表中的字段名，后面的大写A是excel中位置
$data['idCard'] =$objPHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
$data['studentID'] =$objPHPExcel->getActiveSheet()->getCell("B" .$i)->getValue();
$data['studentName'] =$objPHPExcel->getActiveSheet()->getCell("C" .$i)->getValue();
$data['forecast'] = $objPHPExcel->getActiveSheet()->getCell("D". $i)->getValue();
$data['studentSex'] = $objPHPExcel->getActiveSheet()->getCell("E". $i)->getValue();
$data['studentNumber'] = $objPHPExcel->getActiveSheet()->getCell("F". $i)->getValue();
$data['dormitoryGrade'] = $objPHPExcel->getActiveSheet()->getCell("G". $i)->getValue();
$data['dormitory'] = $objPHPExcel->getActiveSheet()->getCell("H". $i)->getValue();
$data['report'] = $objPHPExcel->getActiveSheet()->getCell("I". $i)->getValue();
$data['address'] = $objPHPExcel->getActiveSheet()->getCell("J". $i)->getValue();
$data['sick'] = $objPHPExcel->getActiveSheet()->getCell("K". $i)->getValue();
$data['prove'] = $objPHPExcel->getActiveSheet()->getCell("L". $i)->getValue();
$data['insurance'] = $objPHPExcel->getActiveSheet()->getCell("M". $i)->getValue();
$data['className'] = $objPHPExcel->getActiveSheet()->getCell("N". $i)->getValue();
$data['speName'] = $objPHPExcel->getActiveSheet()->getCell("O". $i)->getValue();
//看这里看这里,这个位置写数据库中的表名

D('student')->add($data);
}
$this->success('导入成功!');
} else {
$this->error("请选择上传的文件");
}
}*/
/*    //导出Excel
    public function out()
    {
        
        //导出
        $path = dirname(__FILE__); //找到当前脚本所在路径
        vendor("PHPExcel.PHPExcel.PHPExcel");
        vendor("PHPExcel.PHPExcel.Writer.IWriter");
        vendor("PHPExcel.PHPExcel.Writer.Abstract");
        vendor("PHPExcel.PHPExcel.Writer.Excel5");
        vendor("PHPExcel.PHPExcel.Writer.Excel2007");
        vendor("PHPExcel.PHPExcel.IOFactory");
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);


        // 实例化完了之后就先把数据库里面的数据查出来
        $student = M('student');
        $sql = $student->select();

        // 设置表头信息
        $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('A1', '身份证')
        ->setCellValue('B1', '学号')
        ->setCellValue('C1', '姓名')
        ->setCellValue('D1', '是否预报')
        ->setCellValue('E1', '性别')
        ->setCellValue('F1', '电话')
        ->setCellValue('G1', '宿舍等级')
        ->setCellValue('H1', '宿舍号')
        ->setCellValue('I1', '是否预报')
        ->setCellValue('J1', '住址')
        ->setCellValue('K1', '有无病史')
        ->setCellValue('L1', '贫困证明')
        ->setCellValue('M1', '有无保险')
        ->setCellValue('N1', '班级')
        ->setCellValue('O1', '专业');

        //--------------开始从数据库提取信息插入Excel表中------------------

        $i=2;  //定义一个i变量，目的是在循环输出数据是控制行数
        $count = count($sql);  //计算有多少条数据
        for ($i = 2; $i <= $count+1; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $sql[$i-2]['idcard']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $sql[$i-2]['studentid']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $sql[$i-2]['studentname']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $sql[$i-2]['forecast']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $sql[$i-2]['studentsex']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $sql[$i-2]['studentnumber']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $sql[$i-2]['dormitorygrade']);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $sql[$i-2]['dormitory']);
            $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $sql[$i-2]['report']);
            $objPHPExcel->getActiveSheet()->setCellValue('J' . $i, $sql[$i-2]['address']);
            $objPHPExcel->getActiveSheet()->setCellValue('K' . $i, $sql[$i-2]['sick']);
            $objPHPExcel->getActiveSheet()->setCellValue('L' . $i, $sql[$i-2]['prove']);
            $objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $sql[$i-2]['insurance']);
            $objPHPExcel->getActiveSheet()->setCellValue('N' . $i, $sql[$i-2]['classname']);
            $objPHPExcel->getActiveSheet()->setCellValue('O' . $i, $sql[$i-2]['spename']);
        }

        
       //--------------下面是设置其他信息------------------

        $objPHPExcel->getActiveSheet()->setTitle('productaccess');      //设置sheet的名称
        $objPHPExcel->setActiveSheetIndex(0);                   //设置sheet的起始位置
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');   //通过PHPExcel_IOFactory的写函数将上面数据写出来
        
        $PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
            
        header('Content-Disposition: attachment;filename="学生列表.xlsx"');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        
        $PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
        
    }*/
   public function student_excel(){
    	if(IS_POST){
    		 if (!empty($_FILES)) {
	            $upload = new \Think\Upload();
	            $upload->maxSize = 3145728 ;// 设置附件上传大小
	            $upload->exts = array('xlsx','xls');// 设置附件上传类型
	            $upload->rootPath ='./Upload/Excel/';
	            // 上传文件，保存到upload下，在跟目录下建upload/Excel文件夹
	            $info = $upload->upload();
	            if(!$info) {
					// 上传错误提示错误信息
	                $this->error($upload->getError());
	            }else{
					// 上传成功 获取上传文件信息
	                $file_name=$upload->rootPath.$info['excel']['savepath'].$info['excel']["savename"];//地址等于更目录加上创建的子目录加上文件名
	                $data = D('student')->readExcel($file_name,'xls');
					$msg = D('student')->saveExcel($data);
					$this->success('导入成功',U('Superadmin/showuserapply'));
	            }
	        }

    	}
        //$this->display('Superadmin/index');
    }

    
    function outExcel(){
        $data = M('student')->select();
        $field = array(
            'A' => array('idcard', '身份证'),
            'B' => array('studentid', '学号'),
            'C' => array('studentname', '姓名'),
            'D' => array('forecast', '是否报到'),
            'E' => array('studentsex', '性别'),
            'F' => array('studentnumber', '电话号码'),
            'G' => array('dormitorygrade', '宿舍级别'),
            'H' => array('dormitory', '宿舍号码'),
            'I' => array('report', '是否预报'),
            'J' => array('address', '家庭住址'),
            'K' => array('sick', '有无病史'),
            'L' => array('prove', '贫困证明'),
            'M' => array('insurance', '有无保险'),
            'N' => array('classname', '班级'),
            'O' => array('spename', '专有名称'),
        );
  
        $this->phpExcelList($field, $data, '班级列表_' . date('Y-m-d'));
    	
    }
    /*-------------------------班级导出----------------------------*/
    function classExcel(){
       	$classname = I('get.classname');
       	$map = array('className'=>$classname);
        $data = M('student')->where($map)->select();
        $field = array(
            'A' => array('idcard', '身份证'),
            'B' => array('studentid', '学号'),
            'C' => array('studentname', '姓名'),
            'D' => array('forecast', '是否报到'),
            'E' => array('studentsex', '性别'),
            'F' => array('studentnumber', '电话号码'),
            'G' => array('dormitorygrade', '宿舍级别'),
            'H' => array('dormitory', '宿舍号码'),
            'I' => array('report', '是否预报'),
            'J' => array('address', '家庭住址'),
            'K' => array('sick', '有无病史'),
            'L' => array('prove', '贫困证明'),
            'M' => array('insurance', '有无保险'),
            'N' => array('classname', '班级'),
            'O' => array('spename', '专有名称'),
        );
    	if ($data) {
    		    $this->phpExcelList($field, $data, '班级列表_' . date('Y-m-d'));
                $this->success('导入成功',U('statistical'));
            } else {
                $this->error('导入失败');
            }    
        
    }
    /*-------------------------系别导出----------------------------*/
    function speExcel(){
       	$spename = I('get.spename');
       	$map = array('speName'=>$spename);
        $data = M('student')->where($map)->select();
        $field = array(
            'A' => array('idcard', '身份证'),
            'B' => array('studentid', '学号'),
            'C' => array('studentname', '姓名'),
            'D' => array('forecast', '是否报到'),
            'E' => array('studentsex', '性别'),
            'F' => array('studentnumber', '电话号码'),
            'G' => array('dormitorygrade', '宿舍级别'),
            'H' => array('dormitory', '宿舍号码'),
            'I' => array('report', '是否预报'),
            'J' => array('address', '家庭住址'),
            'K' => array('sick', '有无病史'),
            'L' => array('prove', '贫困证明'),
            'M' => array('insurance', '有无保险'),
            'N' => array('classname', '班级'),
            'O' => array('spename', '专有名称'),
        );
    	if ($data) {
    		    $this->phpExcelList($field, $data, '班级列表_' . date('Y-m-d'));
                $this->success('导入成功',U('statistical'));
            } else {
                $this->error('导入失败');
            }    
        
    }

  public function phpExcelList($field, $list, $title='文件')
    {
        vendor('phpExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel); //设置保存版本格式
        foreach ($list as $key => $value) {
            foreach ($field as $k => $v) {
                if ($key == 0) {
                    $objPHPExcel->getActiveSheet()->setCellValue($k . '1', $v[1]);
                }
                $i = $key + 2; //表格是从2开始的
                $objPHPExcel->getActiveSheet()->setCellValue($k . $i, $value[$v[0]]);
            }

        }
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename='.$title.'.xls');
        header("Content-Transfer-Encoding:binary");
//        $objWriter->save($title.'.xls');
        $objWriter->save('php://output');
    }  
}
?>